import pymysql

# （1）连接数据库
from a1.duqu import pdfs

connect = pymysql.connect(
    host='localhost',
    user='root',
    passwd="123456",
    charset="utf8",
    autocommit=True
)

cur = connect.cursor()

if connect:
    print("数据库连接成功！")

# （2）创建数据库
create_db = "CREATE DATABASE IF NOT EXISTS `pdf_info`;"
try:
    cur.execute(create_db)
    cur.execute("use pdf_info")
except Exception as e:
    print("数据库创建失败:", e)
else:
    print("数据库创建成功！")

# （3）创建数据表
create_table = "CREATE TABLE IF NOT EXISTS info (t1 VARCHAR(100),pro_name VARCHAR(100), code VARCHAR(100), " \
               "num VARCHAR(100),date VARCHAR(100),year VARCHAR(100),month VARCHAR(100),day VARCHAR(100), client_name " \
               "VARCHAR(100),client_itin VARCHAR(100), seller_name VARCHAR(100),seller_itin VARCHAR(100), " \
               "car_num VARCHAR(100),car_type VARCHAR(100),total_price VARCHAR(100),price VARCHAR(100)," \
               "tax_rate VARCHAR(100),tax_price VARCHAR(100),dir VARCHAR(200),path VARCHAR(200),file VARCHAR(200))"
try:
    cur.execute(create_table)
except Exception as e:
    print("数据表创建失败:", e)
else:
    print("数据表创建成功！")


# （4）插入数据
try:
    for file in pdfs:
        # 确保 file 是字典
        if not isinstance(file, dict):
            print("Error: file is not a dictionary, it's a", type(file))
            continue
        # 使用参数化查询
        columns = ", ".join(file.keys())
        placeholders = ", ".join(["%s"] * len(file))
        insert_data = f"INSERT INTO `info` ({columns}) VALUES ({placeholders})"
        data_tuple = tuple(file.values())
        # 确保占位符数量与 data_tuple 元素数量一致
        if insert_data.count('%s') != len(data_tuple):
            print("Error: The number of placeholders does not match the number of values.")
            continue
        cur.execute(insert_data, data_tuple)
except Exception as e:
    print("数据信息插入失败:", e)
else:
    print("信息插入成功！")
# （5）查询数据
try:
    query_info = "select * from info;"
    cur.execute(query_info)
    res = cur.fetchall()
except Exception as e:
    print("查询数据失败:", e)
else:
    print("数据如下：")
    for i in res:
        print(i)

# （6）关闭数据库连接
cur.close()
connect.close()